/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.sadhar.sipp.laporanrabunit;

import java.util.List;
import java.util.Map;
import org.sadhar.errhandler.ClassAntiNull;
import org.sadhar.sia.common.ClassConnection;
import org.sadhar.sia.common.ClassUtility;

/**
 *
 * @author jasoet
 */
public class LaporanRABUnitDAOImpl implements LaporanRABUnitDAO {

    @Override
    public String tahunKelompokAnggaranYangDigunakan(String tahunAnggaran) {
        String sql = "select tahunKelompok from ppmk.kelompokAnggaranYangDigunakan where tahunAnggaran=?";
        List<Map> lmap = ClassConnection.getJdbc().queryForList(
                sql,
                new Object[]{ClassUtility.quote(tahunAnggaran)});

        String tahunKelompok = "";
        for (Map m : lmap) {
            tahunKelompok = ClassAntiNull.AntiNullString(m.get("tahunKelompok"));
        }

        return tahunKelompok;
    }

    @Override
    public String getQueryUnitNonRapat(String tahun, String tahunKelompok, String kodeUnit) {
        String sql = "select Nama_unit_kerja,namaPosBiaya as nmPos1,namaPosBiaya as nmPos2,namaItem,harga,kuantitas,(harga*kuantitas)"
                + " from ppmk.anggaranRABNonRapatKerja as rab inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya"
                + " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya"
                + " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya"
                + " inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit"
                + " inner join ppmk.itemBiaya as item on item.kodeItem=rab.kodeItemBiayaBarang"
                + " where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun='" + tahun + "' and klpk.tahunKelompok='" + tahunKelompok + "' and pos.namaTabelItem='itemBiaya' and kodeUnit='" + kodeUnit + "'"
                + " union all"
                + " select Nama_unit_kerja,namaPosBiaya as nmPos1,namaPosBiaya as nmPos2,namaBarang,harga,kuantitas,(harga*kuantitas)"
                + " from ppmk.anggaranRABNonRapatKerja as rab inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya"
                + " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya"
                + " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya"
                + " inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit"
                + " inner join asset.hargaBarang as harga on harga.idHargaBarang=rab.kodeItemBiayaBarang"
                + " inner join asset.barang as item on item.idBarang=harga.idBarang"
                + "  where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun='" + tahun + "' and klpk.tahunKelompok='" + tahunKelompok + "' and pos.namaTabelItem='barang' and kodeUnit='" + kodeUnit + "' order by Nama_unit_kerja";
        return sql;
    }

    public String getQueryBiroKeuanganRapat(String tahun, String tahunKelompok, String kodeUnit) {
        String sql = "select Nama_unit_kerja,pos.namaPosBiaya as nmPos1, namaRapatKerja,pos2.namaPosBiaya as nmPos2, namaItem,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari)) as total "
                + " from ppmk.kelompokBiaya as klpk"
                + " inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya"
                + " inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya"
                + " inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit"
                + " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya"
                + " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya"
                + " inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit"
                + " inner join ppmk.itemBiaya as item on item.kodeItem=rab_rapat.kodeItemBiayaBarang"
                + " inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya"
                + " inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja"
                + " where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok='" + tahunKelompok + "' and rapat.tahun='" + tahun + "' and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='itemBiaya' and kodeUnit='" + kodeUnit + "'"
                + " union all"
                + " select Nama_unit_kerja,pos.namaPosBiaya as nmPos1, namaRapatKerja,pos2.namaPosBiaya as nmPos2,namaBarang,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari)) as total "
                + " from ppmk.kelompokBiaya as klpk"
                + " inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya"
                + " inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya"
                + " inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit"
                + " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya"
                + " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya"
                + " inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit"
                + " inner join asset.hargaBarang as harga on harga.idHargaBarang=rab_rapat.kodeItemBiayaBarang"
                + " inner join asset.barang as item on item.idBarang=harga.idBarang"
                + " inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya"
                + " inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja"
                + " where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok='" + tahunKelompok + "'  and rapat.tahun='" + tahun + "' and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='barang' and kodeUnit='" + kodeUnit + "' order by Nama_unit_kerja,nmPos2,namaRapatKerja";

        return sql;
    }

    public String getQueryBiroKeuanganRapat(String tahun, String tahunKelompok) {
        String sql = "select Nama_unit_kerja,pos.namaPosBiaya as nmPos1, namaRapatKerja,pos2.namaPosBiaya as nmPos2, namaItem,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))  as total "
                + " from ppmk.kelompokBiaya as klpk"
                + " inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya"
                + " inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya"
                + " inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit"
                + " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya"
                + " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya"
                + " inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit"
                + " inner join ppmk.itemBiaya as item on item.kodeItem=rab_rapat.kodeItemBiayaBarang"
                + " inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya"
                + " inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja"
                + " where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok='" + tahunKelompok + "' and rapat.tahun='" + tahun + "' and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='itemBiaya'"
                + " union all"
                + " select Nama_unit_kerja,pos.namaPosBiaya as nmPos1, namaRapatKerja,pos2.namaPosBiaya as nmPos2,namaBarang,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))  as total "
                + " from ppmk.kelompokBiaya as klpk"
                + " inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya"
                + " inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya"
                + " inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit"
                + " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya"
                + " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya"
                + " inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit"
                + " inner join asset.hargaBarang as harga on harga.idHargaBarang=rab_rapat.kodeItemBiayaBarang"
                + " inner join asset.barang as item on item.idBarang=harga.idBarang"
                + " inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya"
                + " inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja"
                + " where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok='" + tahunKelompok + "'  and rapat.tahun='" + tahun + "' and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='barang' order by Nama_unit_kerja,nmPos2,namaRapatKerja";

        return sql;
    }

    public String getQueryBiroKeuanganNonRapat(String tahun, String tahunKelompok) {
        String sql = "select Nama_unit_kerja,namaPosBiaya as nmPos1,namaPosBiaya as nmPos2,namaItem,harga,kuantitas,(harga*kuantitas)"
                + " from ppmk.anggaranRABNonRapatKerja as rab"
                + " inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya"
                + " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya"
                + " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya"
                + " inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit"
                + " inner join ppmk.itemBiaya as item on item.kodeItem=rab.kodeItemBiayaBarang"
                + " where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun='" + tahun + "' and klpk.tahunKelompok='" + tahunKelompok + "' and pos.namaTabelItem='itemBiaya'"
                + " union all"
                + " select Nama_unit_kerja,namaPosBiaya as nmPos1,namaPosBiaya as nmPos2,namaBarang,harga,kuantitas,(harga*kuantitas)"
                + " from ppmk.anggaranRABNonRapatKerja as rab"
                + " inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya"
                + " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya"
                + " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya"
                + " inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit"
                + " inner join asset.hargaBarang as harga on harga.idHargaBarang=rab.kodeItemBiayaBarang"
                + " inner join asset.barang as item on item.idBarang=harga.idBarang"
                + " where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun='" + tahun + "' and klpk.tahunKelompok='" + tahunKelompok + "' and pos.namaTabelItem='barang' order by Nama_unit_kerja";
        return sql;
    }

    public String getQueryBiroKeuanganNonRapat(String tahun, String tahunKelompok, String kodeUnit) {
        String sql = "select Nama_unit_kerja,namaPosBiaya as nmPos1,namaPosBiaya as nmPos2,namaItem,harga,kuantitas,(harga*kuantitas)"
                + " from ppmk.anggaranRABNonRapatKerja as rab"
                + " inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya"
                + " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya"
                + " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya"
                + " inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit"
                + " inner join ppmk.itemBiaya as item on item.kodeItem=rab.kodeItemBiayaBarang"
                + " where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun='" + tahun + "' and klpk.tahunKelompok='" + tahunKelompok + "' and pos.namaTabelItem='itemBiaya' and kodeUnit='" + kodeUnit + "'"
                + " union all"
                + " select Nama_unit_kerja,namaPosBiaya as nmPos1,namaPosBiaya as nmPos2,namaBarang,harga,kuantitas,(harga*kuantitas)"
                + " from ppmk.anggaranRABNonRapatKerja as rab"
                + " inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya "
                + " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya"
                + " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya"
                + " inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit"
                + " inner join asset.hargaBarang as harga on harga.idHargaBarang=rab.kodeItemBiayaBarang"
                + " inner join asset.barang as item on item.idBarang=harga.idBarang where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun='" + tahun + "' and klpk.tahunKelompok='" + tahunKelompok + "' and pos.namaTabelItem='barang' and kodeUnit='" + kodeUnit + "' order by Nama_unit_kerja";

        return sql;
    }

    public String getQueryUnitRapat(String tahun, String tahunKelompok, String kodeUnit) {
        String sql = "select Nama_unit_kerja,pos.namaPosBiaya as nmPos1, namaRapatKerja,pos2.namaPosBiaya as nmPos2, namaItem,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))  as total "
                + " from ppmk.kelompokBiaya as klpk"
                + " inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya"
                + " inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya"
                + " inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit"
                + " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya"
                + " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya"
                + " inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit"
                + " inner join ppmk.itemBiaya as item on item.kodeItem=rab_rapat.kodeItemBiayaBarang"
                + " inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya"
                + " inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja"
                + " where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok='" + tahunKelompok + "' and rapat.tahun='" + tahun + "' and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='itemBiaya' and kodeUnit='" + kodeUnit + "'"
                + " union all"
                + " select Nama_unit_kerja,pos.namaPosBiaya as nmPos1, namaRapatKerja,pos2.namaPosBiaya as nmPos2,namaBarang,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))  as total "
                + " from ppmk.kelompokBiaya as klpk"
                + " inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya"
                + " inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya"
                + " inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit"
                + " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya"
                + " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya"
                + " inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit"
                + " inner join asset.hargaBarang as harga on harga.idHargaBarang=rab_rapat.kodeItemBiayaBarang"
                + " inner join asset.barang as item on item.idBarang=harga.idBarang"
                + " inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya"
                + " inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja"
                + " where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok='" + tahunKelompok + "'  and rapat.tahun='" + tahun + "' and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='barang' and kodeUnit='" + kodeUnit + "' order by Nama_unit_kerja,nmPos2,namaRapatKerja";

        return sql;
    }

    public String getQueryBiayaGaji(String tahun) {
        String sql = "SELECT kodeUnit,Nama_unit_kerja,adm,Nm_stat_edu,IF(isKomponenGaji='N',komponenDiluarGaji,(SELECT nmKomponen FROM penggajian.komponengaji AS kg WHERE kg.kdKomponen=kabg.kodeKomponenGaji)) AS komponen,nominalPerBulan "
                + " FROM ppmk.komponenAnggaranBiayaGaji AS kabg "
                + " INNER JOIN ppmk.anggaranBiayaGaji AS abg ON abg.idKomponen=kabg.idKomponen "
                + " INNER JOIN kamus.stat_edu AS stat ON stat.Kd_stat_edu=kabg.adm "
                + " INNER JOIN kamus.unkerja AS un ON un.Kd_unit_kerja=abg.kodeUnit "
                + " WHERE abg.tahun='" + tahun + "' "
                + " GROUP BY kodeUnit, adm ORDER BY kodeUnit, adm;";
        return sql;
    }

    public String getQueryBiayaGaji(String tahun, String kodeUnit) {
        String sql = "SELECT kodeUnit,Nama_unit_kerja,adm,Nm_stat_edu,IF(isKomponenGaji='N',komponenDiluarGaji,(SELECT nmKomponen FROM penggajian.komponengaji AS kg WHERE kg.kdKomponen=kabg.kodeKomponenGaji)) AS komponen,nominalPerBulan "
                + " FROM ppmk.komponenAnggaranBiayaGaji AS kabg "
                + " INNER JOIN ppmk.anggaranBiayaGaji AS abg ON abg.idKomponen=kabg.idKomponen "
                + " INNER JOIN kamus.stat_edu AS stat ON stat.Kd_stat_edu=kabg.adm "
                + " INNER JOIN kamus.unkerja AS un ON un.Kd_unit_kerja=abg.kodeUnit "
                + " WHERE abg.tahun='" + tahun + "' AND kodeUnit='" + kodeUnit + "' "
                + " GROUP BY kodeUnit, adm ORDER BY kodeUnit, adm;";
        return sql;
    }

    public String getQueryRABUnit(String tahun, String tahunKelompok, String kodeUnit) {

        String sql = "select Nama_unit_kerja,namaPosBiaya as nmPos1,namaPosBiaya as nmPos2,namaItem,harga,kuantitas,(harga*kuantitas) as total  "
                + " from ppmk.anggaranRABNonRapatKerja as rab "
                + " inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya"
                + " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya"
                + " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya"
                + " inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit"
                + " inner join ppmk.itemBiaya as item on item.kodeItem=rab.kodeItemBiayaBarang"
                + " where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun='" + tahun + "' and klpk.tahunKelompok='" + tahunKelompok + "' and pos.namaTabelItem='itemBiaya' and rab.kodeUnit='" + kodeUnit + "'" + //tahun ? ambil dari session. tahunKelompok ? dikueri dulu dari tabel kelompokAnggaranYangDigunakan, mirip yang tadi perekaman, kodeUnit ? dari session
                " union all"
                + " select Nama_unit_kerja,namaPosBiaya as nmPos1,namaPosBiaya as nmPos2,namaBarang,harga,kuantitas,(harga*kuantitas) as total "
                + " from ppmk.anggaranRABNonRapatKerja as rab inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya"
                + " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya"
                + " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya"
                + " inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit"
                + " inner join asset.hargaBarang as harga on harga.idHargaBarang=rab.kodeItemBiayaBarang"
                + " inner join asset.barang as item on item.idBarang=harga.idBarang"
                + " where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun='" + tahun + "' and klpk.tahunKelompok='" + tahunKelompok + "' and pos.namaTabelItem='barang' and rab.kodeUnit='" + kodeUnit + "'" + //tahun ? ambil dari session. tahunKelompok ? dikueri dulu dari tabel kelompokAnggaranYangDigunakan, mirip yang tadi perekaman, kodeUnit ? dari session
                " union all"
                + " select Nama_unit_kerja,pos.namaPosBiaya as nmPos1, pos2.namaPosBiaya as nmPos2, namaItem,harga,kuantitas,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))  as total "
                + " from ppmk.kelompokBiaya as klpk"
                + " inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya"
                + " inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya"
                + " inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit"
                + " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya"
                + " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya"
                + " inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit"
                + " inner join ppmk.itemBiaya as item on item.kodeItem=rab_rapat.kodeItemBiayaBarang"
                + " inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya"
                + " where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5  and rapat.tahun='" + tahun + "' and tahunKelompok='" + tahunKelompok + "' and isRapat='Y' and isRevisi='Y' and  pos2.namaTabelItem='itemBiaya' and rapat.kodeUnit='" + kodeUnit + "'" + //tahun ? ambil dari session. tahunKelompok ? dikueri dulu dari tabel kelompokAnggaranYangDigunakan, mirip yang tadi perekaman, kodeUnit ? dari session
                " union all"
                + " select Nama_unit_kerja,pos.namaPosBiaya as nmPos1, pos2.namaPosBiaya as nmPos2,namaBarang,harga,kuantitas,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))  as total "
                + " from ppmk.kelompokBiaya as klpk"
                + " inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya"
                + " inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya"
                + " inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit"
                + " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya"
                + " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya"
                + " inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit"
                + " inner join asset.hargaBarang as harga on harga.idHargaBarang=rab_rapat.kodeItemBiayaBarang"
                + " inner join asset.barang as item on item.idBarang=harga.idBarang"
                + " inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya"
                + " where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok='" + tahunKelompok + "' and rapat.tahun='" + tahun + "' and isRapat='Y' and isRevisi='Y' and pos2.namaTabelItem='barang' and rapat.kodeUnit='" + kodeUnit + "'"; //tahun ? ambil dari session. tahunKelompok ? dikueri dulu dari tabel kelompokAnggaranYangDigunakan, mirip yang tadi perekaman, kodeUnit ? dari session

        return sql;
    }
}
// a. untuk unit
// rab  non rapat kerja
/* kueri asli dari bapsi
select Nama_unit_kerja,namaPosBiaya as nmPos1,namaItem,harga,kuantitas,(harga*kuantitas)
from ppmk.anggaranRABNonRapatKerja as rab inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya
inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya
inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya
inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit
inner join ppmk.itemBiaya as item on item.kodeItem=rab.kodeItemBiayaBarang
where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun='2009' and klpk.tahunKelompok='2009' and pos.namaTabelItem='itemBiaya' and kodeUnit='03001040'
union all
select Nama_unit_kerja,namaPosBiaya as nmPos1,namaBarang,harga,kuantitas,(harga*kuantitas)
from ppmk.anggaranRABNonRapatKerja as rab inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya
inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya
inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya
inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit
inner join asset.hargaBarang as harga on harga.idHargaBarang=rab.kodeItemBiayaBarang
inner join asset.barang as item on item.idBarang=harga.idBarang
where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun='2009' and klpk.tahunKelompok='2009' and pos.namaTabelItem='barang' and kodeUnit='03001040' order by Nama_unit_kerja;

 * /////////////////////////
kueri diatas diformat yg yg ada ?nya
//////////
"select Nama_unit_kerja,namaPosBiaya as nmPos1,namaItem,harga,kuantitas,(harga*kuantitas)" +
"from ppmk.anggaranRABNonRapatKerja as rab inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya" +
"inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya" +
"inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya" +
"inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit" +
"inner join ppmk.itemBiaya as item on item.kodeItem=rab.kodeItemBiayaBarang" +
"where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun=? and klpk.tahunKelompok=? and pos.namaTabelItem='itemBiaya' and kodeUnit=?" +
"union all" +
"select Nama_unit_kerja,namaPosBiaya as nmPos1,namaBarang,harga,kuantitas,(harga*kuantitas)" +
"from ppmk.anggaranRABNonRapatKerja as rab inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya" +
"inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya" +
"inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya" +
"inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit" +
"inner join asset.hargaBarang as harga on harga.idHargaBarang=rab.kodeItemBiayaBarang" +
"inner join asset.barang as item on item.idBarang=harga.idBarang" +
"where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun=? and klpk.tahunKelompok=? and pos.namaTabelItem='barang' and kodeUnit=? order by Nama_unit_kerja";
//////////////////////////////////////////////////////////////////////
 */
// rab rapat kerja
/*kueri asli dari bapsi
select Nama_unit_kerja,pos.namaPosBiaya as nmPos3, namaRapatKerja,pos2.namaPosBiaya as nmPos2, namaItem,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))
from ppmk.kelompokBiaya as klpk
inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya
inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya
inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit
inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya
inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya
inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit
inner join ppmk.itemBiaya as item on item.kodeItem=rab_rapat.kodeItemBiayaBarang
inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya
inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja
where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok='2009' and rapat.tahun='2009' and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='itemBiaya' and kodeUnit='03001040'
union all
select Nama_unit_kerja,pos.namaPosBiaya as nmPos3, namaRapatKerja,pos2.namaPosBiaya as nmPos2,namaBarang,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))
from ppmk.kelompokBiaya as klpk
inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya
inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya
inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit
inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya
inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya
inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit
inner join asset.hargaBarang as harga on harga.idHargaBarang=rab_rapat.kodeItemBiayaBarang
inner join asset.barang as item on item.idBarang=harga.idBarang
inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya
inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja
where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok='2009'  and rapat.tahun='2009' and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='barang' and kodeUnit='03001040' order by Nama_unit_kerja,nmPos3,namaRapatKerja;

 * /////////////////////////
kueri diatas diformat yg yg ada ?nya
//////////
"select Nama_unit_kerja,pos.namaPosBiaya as nmPos3, namaRapatKerja,pos2.namaPosBiaya as nmPos2, namaItem,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))" +
"from ppmk.kelompokBiaya as klpk" +
"inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya" +
"inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya" +
"inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit" +
"inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya" +
"inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya" +
"inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit" +
"inner join ppmk.itemBiaya as item on item.kodeItem=rab_rapat.kodeItemBiayaBarang" +
"inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya" +
"inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja" +
"where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok=? and rapat.tahun=? and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='itemBiaya' and kodeUnit=?" +
"union all" +
"select Nama_unit_kerja,pos.namaPosBiaya as nmPos3, namaRapatKerja,pos2.namaPosBiaya as nmPos2,namaBarang,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))" +
"from ppmk.kelompokBiaya as klpk" +
"inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya" +
"inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya" +
"inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit" +
"inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya" +
"inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya" +
"inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit" +
"inner join asset.hargaBarang as harga on harga.idHargaBarang=rab_rapat.kodeItemBiayaBarang" +
"inner join asset.barang as item on item.idBarang=harga.idBarang" +
"inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya" +
"inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja" +
"where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok=?  and rapat.tahun=? and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='barang' and kodeUnit=? order by Nama_unit_kerja,nmPos3,namaRapatKerja";

 */
//b. untuk biro keuangan
//RAB non rapat kerja
//* 1. jika user memilih dari search unit kerja
 /*
 *kueri asli dari bapsi
select Nama_unit_kerja,namaPosBiaya as nmPos1,namaItem,harga,kuantitas,(harga*kuantitas)
from ppmk.anggaranRABNonRapatKerja as rab
inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya
inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya
inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya
inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit
inner join ppmk.itemBiaya as item on item.kodeItem=rab.kodeItemBiayaBarang
where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun='2009' and klpk.tahunKelompok='2009' and pos.namaTabelItem='itemBiaya' and kodeUnit='03001040'
union all
select Nama_unit_kerja,namaPosBiaya as nmPos1,namaBarang,harga,kuantitas,(harga*kuantitas)
from ppmk.anggaranRABNonRapatKerja as rab
inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya
inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya
inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya
inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit
inner join asset.hargaBarang as harga on harga.idHargaBarang=rab.kodeItemBiayaBarang
inner join asset.barang as item on item.idBarang=harga.idBarang where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun='2009' and klpk.tahunKelompok='2009' and pos.namaTabelItem='barang' and kodeUnit='03001040' order by Nama_unit_kerja;

 *  * /////////////////////////
kueri diatas diformat yg yg ada ?nya
//////////
 *
"select Nama_unit_kerja,namaPosBiaya as nmPos1,namaItem,harga,kuantitas,(harga*kuantitas)" +
"from ppmk.anggaranRABNonRapatKerja as rab" +
"inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya" +
"inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya" +
"inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya" +
"inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit" +
"inner join ppmk.itemBiaya as item on item.kodeItem=rab.kodeItemBiayaBarang" +
"where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun=? and klpk.tahunKelompok=? and pos.namaTabelItem='itemBiaya' and kodeUnit=?" +
"union all" +
"select Nama_unit_kerja,namaPosBiaya as nmPos1,namaBarang,harga,kuantitas,(harga*kuantitas)" +
"from ppmk.anggaranRABNonRapatKerja as rab" +
"inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya " +
"inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya" +
"inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya" +
"inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit" +
"inner join asset.hargaBarang as harga on harga.idHargaBarang=rab.kodeItemBiayaBarang" +
"inner join asset.barang as item on item.idBarang=harga.idBarang where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun=? and klpk.tahunKelompok=? and pos.namaTabelItem='barang' and kodeUnit=? order by Nama_unit_kerja";

 */
// 2. jika user tidak memilih unit
/* kueri dari bapso
select Nama_unit_kerja,namaPosBiaya as nmPos1,namaItem,harga,kuantitas,(harga*kuantitas)
from ppmk.anggaranRABNonRapatKerja as rab
inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya
inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya
inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya
inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit
inner join ppmk.itemBiaya as item on item.kodeItem=rab.kodeItemBiayaBarang
where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun='2009' and klpk.tahunKelompok='2009' and pos.namaTabelItem='itemBiaya'
union all
select Nama_unit_kerja,namaPosBiaya as nmPos1,namaBarang,harga,kuantitas,(harga*kuantitas)
from ppmk.anggaranRABNonRapatKerja as rab
inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya
inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya
inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya
inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit
inner join asset.hargaBarang as harga on harga.idHargaBarang=rab.kodeItemBiayaBarang
inner join asset.barang as item on item.idBarang=harga.idBarang
where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun='2009' and klpk.tahunKelompok='2009' and pos.namaTabelItem='barang' order by Nama_unit_kerja;
 *  * /////////////////////////
kueri diatas diformat yg yg ada ?nya
//////////
"select Nama_unit_kerja,namaPosBiaya as nmPos1,namaItem,harga,kuantitas,(harga*kuantitas)" +
"from ppmk.anggaranRABNonRapatKerja as rab" +
"inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya" +
"inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya" +
"inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya" +
"inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit" +
"inner join ppmk.itemBiaya as item on item.kodeItem=rab.kodeItemBiayaBarang" +
"where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun=? and klpk.tahunKelompok=? and pos.namaTabelItem='itemBiaya'" +
"union all" +
"select Nama_unit_kerja,namaPosBiaya as nmPos1,namaBarang,harga,kuantitas,(harga*kuantitas)" +
"from ppmk.anggaranRABNonRapatKerja as rab" +
"inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya" +
"inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya" +
"inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya" +
"inner join kamus.unkerja as un on un.Kd_unit_kerja=rab.kodeUnit" +
"inner join asset.hargaBarang as harga on harga.idHargaBarang=rab.kodeItemBiayaBarang" +
"inner join asset.barang as item on item.idBarang=harga.idBarang" +
"where isRevisi='Y' and isSudahDiajukan='Y' and rab.tahun=? and klpk.tahunKelompok=? and pos.namaTabelItem='barang' order by Nama_unit_kerja";

 *
 *
 */
//rab rapat kerja
//1. jika user memilih unit kerja
/* kueri asli dari bapsi
select Nama_unit_kerja,pos.namaPosBiaya as nmPos3, namaRapatKerja,pos2.namaPosBiaya as nmPos2, namaItem,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))
from ppmk.kelompokBiaya as klpk
inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya
inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya
inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit
inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya
inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya
inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit
inner join ppmk.itemBiaya as item on item.kodeItem=rab_rapat.kodeItemBiayaBarang
inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya
inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja
where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok='2009' and rapat.tahun='2009' and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='itemBiaya' and kodeUnit='03001040'
union all
select Nama_unit_kerja,pos.namaPosBiaya as nmPos3, namaRapatKerja,pos2.namaPosBiaya as nmPos2,namaBarang,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))
from ppmk.kelompokBiaya as klpk
inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya
inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya
inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit
inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya
inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya
inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit
inner join asset.hargaBarang as harga on harga.idHargaBarang=rab_rapat.kodeItemBiayaBarang
inner join asset.barang as item on item.idBarang=harga.idBarang
inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya
inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja
where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok='2009'  and rapat.tahun='2009' and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='barang' and kodeUnit='03001040' order by Nama_unit_kerja,nmPos3,namaRapatKerja;
 *  * /////////////////////////
kueri diatas diformat yg yg ada ?nya
//////////
?select Nama_unit_kerja,pos.namaPosBiaya as nmPos3, namaRapatKerja,pos2.namaPosBiaya as nmPos2, namaItem,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))" +
"from ppmk.kelompokBiaya as klpk" +
"inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya" +
"inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya" +
"inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit" +
"inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya" +
"inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya" +
"inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit" +
"inner join ppmk.itemBiaya as item on item.kodeItem=rab_rapat.kodeItemBiayaBarang" +
"inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya" +
"inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja" +
"where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok=? and rapat.tahun=? and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='itemBiaya' and kodeUnit=?" +
"union all" +
"select Nama_unit_kerja,pos.namaPosBiaya as nmPos3, namaRapatKerja,pos2.namaPosBiaya as nmPos2,namaBarang,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))" +
"from ppmk.kelompokBiaya as klpk" +
"inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya" +
"inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya" +
"inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit" +
"inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya" +
"inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya" +
"inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit" +
"inner join asset.hargaBarang as harga on harga.idHargaBarang=rab_rapat.kodeItemBiayaBarang" +
"inner join asset.barang as item on item.idBarang=harga.idBarang" +
"inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya" +
"inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja" +
"where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok=?  and rapat.tahun=? and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='barang' and kodeUnit=? order by Nama_unit_kerja,nmPos3,namaRapatKerja";

 */
//2. jika user tidak memilih unit
/*
select Nama_unit_kerja,pos.namaPosBiaya as nmPos3, namaRapatKerja,pos2.namaPosBiaya as nmPos2, namaItem,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))
from ppmk.kelompokBiaya as klpk
inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya
inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya
inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit
inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya
inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya
inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit
inner join ppmk.itemBiaya as item on item.kodeItem=rab_rapat.kodeItemBiayaBarang
inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya
inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja
where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok='2009' and rapat.tahun='2009' and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='itemBiaya'
union all
select Nama_unit_kerja,pos.namaPosBiaya as nmPos3, namaRapatKerja,pos2.namaPosBiaya as nmPos2,namaBarang,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))
from ppmk.kelompokBiaya as klpk
inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya
inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya
inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit
inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya
inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya
inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit
inner join asset.hargaBarang as harga on harga.idHargaBarang=rab_rapat.kodeItemBiayaBarang
inner join asset.barang as item on item.idBarang=harga.idBarang
inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya
inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja
where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok='2009'  and rapat.tahun='2009' and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='barang' order by Nama_unit_kerja,nmPos3,namaRapatKerja;
 *  * /////////////////////////
kueri diatas diformat yg yg ada ?nya
//////////

"select Nama_unit_kerja,pos.namaPosBiaya as nmPos3, namaRapatKerja,pos2.namaPosBiaya as nmPos2, namaItem,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))" +
"from ppmk.kelompokBiaya as klpk" +
"inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya" +
"inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya" +
"inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit" +
"inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya" +
"inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya" +
"inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit" +
"inner join ppmk.itemBiaya as item on item.kodeItem=rab_rapat.kodeItemBiayaBarang" +
"inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya" +
"inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja" +
"where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok=? and rapat.tahun=? and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='itemBiaya'" +
"union all" +
"select Nama_unit_kerja,pos.namaPosBiaya as nmPos3, namaRapatKerja,pos2.namaPosBiaya as nmPos2,namaBarang,harga,kuantitas,jumlahHari,if(jumlahHari=0,(harga*kuantitas),(harga*kuantitas*jumlahHari))" +
"from ppmk.kelompokBiaya as klpk" +
"inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya" +
"inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya" +
"inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit" +
"inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya" +
"inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya" +
"inner join kamus.unkerja as un on un.Kd_unit_kerja=rapat.kodeUnit" +
"inner join asset.hargaBarang as harga on harga.idHargaBarang=rab_rapat.kodeItemBiayaBarang" +
"inner join asset.barang as item on item.idBarang=harga.idBarang" +
"inner join ppmk.posBiaya as pos2 on pos2.kodePosBiaya=sub.KodePosBiaya" +
"inner join ppmk.rapatKerja as rpt on rpt.kodeRapatKerja=rapat.kodeRapatKerja" +
"where klpk.kodeJenisBiaya=1 and klpk.kodePosBiaya=5 and tahunKelompok=?  and rapat.tahun=? and isRapat='Y' and isRevisi='Y' and isSudahDiajukan='Y' and pos2.namaTabelItem='barang' order by Nama_unit_kerja,nmPos3,namaRapatKerja";

 */
